package zy.dao.sell.displayarea.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sell.displayarea.DisplayAreaDAO;
import zy.entity.sell.displayarea.T_Sell_DisplayArea;
import zy.form.StringForm;
import zy.util.CommonUtil;

@Repository
public class DisplayAreaDAOImpl extends BaseDaoImpl implements DisplayAreaDAO {

	@Override
	public Integer count(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sp_code = params.get("sp_code");//页面传递的店铺编号
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_sell_displayarea t");
		sql.append(" JOIN t_base_shop sp ON sp_code = da_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" AND sp_upcode = :shop_code AND (sp_shop_type = "+CommonUtil.THREE+" OR sp_shop_type = "+CommonUtil.FIVE+")");
		}else if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FOUR.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、加盟店、合伙店
			sql.append(" AND sp_code = :shop_code");
		}
		if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(da_code,:searchContent)>0 OR INSTR(da_name,:searchContent)>0)");
        }
		if(sp_code != null && !"".equals(sp_code)){
			sql.append(" AND da_shop_code = :sp_code");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Sell_DisplayArea> list(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sp_code = params.get("sp_code");//页面传递的店铺编号
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT da_id,da_code,da_name,da_shop_code,t.companyid,sp_name AS shop_name");
		sql.append(" FROM t_sell_displayarea t");
		sql.append(" JOIN t_base_shop sp ON sp_code = da_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" AND sp_upcode = :shop_code AND (sp_shop_type = "+CommonUtil.THREE+" OR sp_shop_type = "+CommonUtil.FIVE+")");
		}else if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FOUR.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、加盟店、合伙店
			sql.append(" AND sp_code = :shop_code");
		}
		if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(da_code,:searchContent)>0 OR INSTR(da_name,:searchContent)>0)");
        }
		if(sp_code != null && !"".equals(sp_code)){
			sql.append(" AND da_shop_code = :sp_code");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY da_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_DisplayArea.class));
	}

	@Override
	public T_Sell_DisplayArea queryByID(Integer da_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT da_id,da_code,da_name,da_shop_code,t.companyid, ");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = da_shop_code AND sp.companyid = t.companyid LIMIT 1) AS shop_name");
		sql.append(" FROM t_sell_displayarea t");
		sql.append(" WHERE da_id=:da_id");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("da_id", da_id),
					new BeanPropertyRowMapper<>(T_Sell_DisplayArea.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void save(T_Sell_DisplayArea displayArea) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_three_code(max(da_code+0)) FROM t_sell_displayarea ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(displayArea), String.class);
		displayArea.setDa_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO t_sell_displayarea");
		sql.append(" (da_code,da_name,da_shop_code,companyid)");
		sql.append(" VALUES(:da_code,:da_name,:da_shop_code,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(displayArea),holder);
		int id = holder.getKey().intValue();
		displayArea.setDa_id(id);
	}

	@Override
	public void update(T_Sell_DisplayArea displayArea) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_displayarea");
		sql.append(" SET da_name=:da_name");
		sql.append(" WHERE da_id=:da_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(displayArea));
	}

	@Override
	public void del(Integer da_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_displayarea");
		sql.append(" WHERE da_id=:da_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("da_id", da_id));
	}

	@Override
	public List<StringForm> listShop(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT da_code as code,da_name name");
		sql.append(" FROM t_sell_displayarea t");
		sql.append(" WHERE da_shop_code=:shop_code");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(StringForm.class));
	}

}
